package yams.repository;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import yams.common.IRepository;
import yams.domain.CheckmeterEntity;

import java.util.List;

/**
 * 抄表持久化服务
 * Created by Luffy on 2017/06/13.
 */
@Repository("checkmeterRepository")
public interface ICheckmeterRepository extends IRepository<CheckmeterEntity> {

    @Query(value = " SELECT rt.pk_id,rt.water,rt.electric,rt.gas,rt.meter_year,rt.meter_month,rt.meter_day," +
            " lt.house_id,lt.house_name,rt.name,rt.code,rt.is_gen_bill  FROM " +
            " (SELECT ht.pk_id house_id,ht.name house_name " +
            " FROM t_contract ct, t_house ht " +
            " WHERE ct.house_id = ht.pk_id " +
            " AND ht.account in (:manager) " +
            " AND ct.status = :status " +
            " ) lt LEFT JOIN t_checkmeter rt " +
            " ON lt.house_id = rt.house_id " +
            " AND rt.meter_year = :year " +
            " AND rt.meter_month = :month " +
            " LIMIT :pageNumber , :pageSize " , nativeQuery = true)
    List<Object[]> queryCheckmeter(@Param("status") Integer status,
                                   @Param("year") String year,
                                   @Param("month") String month,
                                   @Param("pageNumber") Integer pageNumber,
                                   @Param("pageSize") Integer pageSize,
                                   @Param("manager") String manager);

    @Query(value = " SELECT rt.pk_id,rt.water,rt.electric,rt.gas,rt.meter_year,rt.meter_month,rt.meter_day," +
            " lt.house_id,lt.house_name,rt.name,rt.code,rt.is_gen_bill  FROM " +
            " (SELECT ht.pk_id house_id,ht.name house_name " +
            " FROM t_contract ct, t_house ht " +
            " WHERE ct.house_id = ht.pk_id " +
            " AND ht.account in (:manager) " +
            " AND ht.pk_id in ( :houseList ) " +
            " AND ct.status = :status " +
            " ) lt LEFT JOIN t_checkmeter rt " +
            " ON lt.house_id = rt.house_id " +
            " AND rt.meter_year = :year " +
            " AND rt.meter_month = :month " +
            " LIMIT :pageNumber , :pageSize " , nativeQuery = true)
    List<Object[]> queryCheckmeter(@Param("status") Integer status,
                                   @Param("year") String year,
                                   @Param("month") String month,
                                   @Param("pageNumber") Integer pageNumber,
                                   @Param("pageSize") Integer pageSize,
                                   @Param("houseList") List<String> houseList,
                                   @Param("manager") String manager);

    @Query(value = " SELECT count(1)  FROM " +
            " (SELECT ht.pk_id house_id,ht.name house_name " +
            " FROM t_contract ct, t_house ht " +
            " WHERE ct.house_id = ht.pk_id " +
            " AND ht.account in (:manager) " +
            " AND ct.status = :status " +
            " ) lt LEFT JOIN t_checkmeter rt " +
            " ON lt.house_id = rt.house_id " +
            " AND rt.meter_year = :year " +
            " AND rt.meter_month = :month " , nativeQuery = true)
    Long countCheckmeter(@Param("status") Integer status,
                         @Param("year") String year,
                         @Param("month") String month,
                         @Param("manager") String manager);

    @Query(value = " SELECT count(1)  FROM " +
            " (SELECT ht.pk_id house_id,ht.name house_name " +
            " FROM t_contract ct, t_house ht " +
            " WHERE ct.house_id = ht.pk_id " +
            " AND ht.account in (:manager) " +
            " AND ht.pk_id in ( :houseList ) " +
            " AND ct.status = :status " +
            " ) lt LEFT JOIN t_checkmeter rt " +
            " ON lt.house_id = rt.house_id " +
            " AND rt.meter_year = :year " +
            " AND rt.meter_month = :month " , nativeQuery = true)
    Long countCheckmeter(@Param("status") Integer status,
                         @Param("year") String year,
                         @Param("month") String month,
                         @Param("houseList") List<String> houseList,
                         @Param("manager") String manager);







    @Query(value = " SELECT rt.pk_id,rt.water,rt.electric,rt.gas,rt.meter_year,rt.meter_month,rt.meter_day," +
            " lt.house_id,lt.house_name,rt.name,rt.code,rt.is_gen_bill,lt.house_number  FROM " +
            " (SELECT ht.pk_id house_id,ht.name house_name,ht.house_number house_number" +
            " FROM t_contract ct, t_house ht " +
            " WHERE ct.house_id = ht.pk_id " +
            " AND ct.status = :status " +
            " ) lt LEFT JOIN t_checkmeter rt " +
            " ON lt.house_id = rt.house_id " +
            " AND rt.meter_year = :year " +
            " AND rt.meter_month = :month " +
            " LIMIT :pageNumber , :pageSize " , nativeQuery = true)
    List<Object[]> queryCheckmeter(@Param("status") Integer status,
                                   @Param("year") String year,
                                   @Param("month") String month,
                                   @Param("pageNumber") Integer pageNumber,
                                   @Param("pageSize") Integer pageSize);

    @Query(value = " SELECT rt.pk_id,rt.water,rt.electric,rt.gas,rt.meter_year,rt.meter_month,rt.meter_day," +
            " lt.house_id,lt.house_name,rt.name,rt.code,rt.is_gen_bill  FROM " +
            " (SELECT ht.pk_id house_id,ht.name house_name " +
            " FROM t_contract ct, t_house ht " +
            " WHERE ct.house_id = ht.pk_id " +
            " AND ht.pk_id in ( :houseList ) " +
            " AND ct.status = :status " +
            " ) lt LEFT JOIN t_checkmeter rt " +
            " ON lt.house_id = rt.house_id " +
            " AND rt.meter_year = :year " +
            " AND rt.meter_month = :month " +
            " LIMIT :pageNumber , :pageSize " , nativeQuery = true)
    List<Object[]> queryCheckmeter(@Param("status") Integer status,
                                   @Param("year") String year,
                                   @Param("month") String month,
                                   @Param("pageNumber") Integer pageNumber,
                                   @Param("pageSize") Integer pageSize,
                                   @Param("houseList") List<String> houseList);

    @Query(value = " SELECT count(1)  FROM " +
            " (SELECT ht.pk_id house_id,ht.name house_name " +
            " FROM t_contract ct, t_house ht " +
            " WHERE ct.house_id = ht.pk_id " +
            " AND ct.status = :status " +
            " ) lt LEFT JOIN t_checkmeter rt " +
            " ON lt.house_id = rt.house_id " +
            " AND rt.meter_year = :year " +
            " AND rt.meter_month = :month " , nativeQuery = true)
    Long countCheckmeter(@Param("status") Integer status,
                         @Param("year") String year,
                         @Param("month") String month);

    @Query(value = " SELECT count(1)  FROM " +
            " (SELECT ht.pk_id house_id,ht.name house_name " +
            " FROM t_contract ct, t_house ht " +
            " WHERE ct.house_id = ht.pk_id " +
            " AND ht.pk_id in ( :houseList ) " +
            " AND ct.status = :status " +
            " ) lt LEFT JOIN t_checkmeter rt " +
            " ON lt.house_id = rt.house_id " +
            " AND rt.meter_year = :year " +
            " AND rt.meter_month = :month " , nativeQuery = true)
    Long countCheckmeter(@Param("status") Integer status,
                         @Param("year") String year,
                         @Param("month") String month,
                         @Param("houseList") List<String> houseList);


}
